/*
 * # 应用所有视图:
 *  Notifying - 通知
 * 
 * # 描述:
 *  向所有用户提供通知功能，包括页面右下角的未读通知、发送新通知、已发送通知列表以及已接收通知列表。
 * 
 * Copyright (c), 上海沃恩信息科技有限公司.
 * All rights reserved.
 * Licensed under BSD (https://www.pittypat.work/bsd.html).
 */
 
use "pittypat_app";
go

/*
 * # 对象视图 - Sql Server:
 *   Attachment - 通知附件
 * 
 * # 描述:
 *   表示接收消息的一个附件文件。
 */

if object_id(N'"waln_notifying"."v_creating_attachment"', N'V') is null
	exec('create view "waln_notifying"."v_creating_attachment" as select null as "_null"');
go

alter view "waln_notifying"."v_creating_attachment"
as
	select
		a."Id", -- Id
		a."Name", -- 文件名称
		a."Mime", -- MIME 类型
		a."Size", -- 附件文件的大小
		a."Url", -- 文件路径
		a."UploaderId", -- 上传人的 Id
		a."UploadTime" -- 上传时间
	from
		"waln_notifying"."attachments" as a;
go

/*
 * # 对象视图 - Sql Server:
 *   Notification - 通知
 * 
 * # 描述:
 *   一条已经发送或接收到的通知消息。
 */

if object_id(N'"waln_notifying"."v_unread_notification"', N'V') is null
	exec('create view "waln_notifying"."v_unread_notification" as select null as "_null"');
go

alter view "waln_notifying"."v_unread_notification"
as
	select
		n."Id", -- Id
		n."SenderId", -- 发送用户的 Id
		s."Name" as "SenderName", -- 发送人名称
		s."FaceUrl" as "SenderFaceUrl", -- 发送人头像路径
		n."ReceiveTime", -- 接收时间
		n."TopTime", -- 置顶时间
		n."Urgency", -- 紧急程度
		n."Group", -- 分组
		n."Title", -- 通知标题
		n."Summary", -- 通知消息摘要
		n."FuncUrl", -- 目标功能路径
		n."AttachmentId", -- 附件 Id
        n."ReceiverId",
        n."ReadTime"
	from
		"waln_notifying"."notifications" as n
    join 
        "waln_authority"."users" as s on s."Id" = n."SenderId";
go

/*
 * # 对象视图 - Sql Server:
 *   Notification - 通知
 * 
 * # 描述:
 *   一条已经发送或接收到的通知消息。
 */

if object_id(N'"waln_notifying"."v_inbox_notification"', N'V') is null
	exec('create view "waln_notifying"."v_inbox_notification" as select null as "_null"');
go

alter view "waln_notifying"."v_inbox_notification"
as
	select
		n."Id", -- Id
		n."SenderId", -- 发送用户的 Id
		s."Name" as "SenderName", -- 发送人名称
		s."FaceUrl" as "SenderFaceUrl", -- 发送人头像路径
		n."ReceiverId", -- 接收用户的 Id
		n."ReceiveTime", -- 接收时间
		n."ReadTime", -- 阅读时间
		n."TopTime", -- 置顶时间
		n."Urgency", -- 紧急程度
		n."Group", -- 分组
		n."Title", -- 通知标题
		n."Summary", -- 通知消息摘要
		n."BodyType", -- 正文类型代码
		n."FuncUrl", -- 目标功能路径
		n."AttachmentId", -- 附件 Id
		a."Url" as "AttachmentUrl", -- 附件文件路径
		a."Name" as "AttachmentName" -- 附件文件名称
	from
		"waln_notifying"."notifications" as n
    join 
        "waln_authority"."users" as s on s."Id" = n."SenderId"
	left join 
		"waln_notifying"."attachments" as a on a."Id" = n."AttachmentId";
go

/*
 * # 对象视图 - Sql Server:
 *   Attachment - 通知附件
 * 
 * # 描述:
 *   表示接收消息的一个附件文件。
 */

if object_id(N'"waln_notifying"."v_inbox_attachment"', N'V') is null
	exec('create view "waln_notifying"."v_inbox_attachment" as select null as "_null"');
go

alter view "waln_notifying"."v_inbox_attachment"
as
	select
		a."Id", -- Id
		a."Name", -- 文件名称
		a."Mime", -- MIME 类型
		a."Size", -- 附件文件的大小
		a."Url", -- 文件路径
		a."UploaderId", -- 上传人的 Id
		a."UploadTime" -- 上传时间
	from
		"waln_notifying"."attachments" as a;
go

/*
 * # 对象视图 - Sql Server:
 *   Notification - 通知
 * 
 * # 描述:
 *   一条已经发送或接收到的通知消息。
 */

if object_id(N'"waln_notifying"."v_outbox_notification"', N'V') is null
	exec('create view "waln_notifying"."v_outbox_notification" as select null as "_null"');
go

alter view "waln_notifying"."v_outbox_notification"
as
	select
		n."PK",
		n."Id", -- Id
		n."SenderId", -- 发送用户的 Id
		n."ReceiverId", -- 接收用户的 Id
		u."Name" as "ReceiverName", -- 接收人名称
		u."FaceUrl" as "ReceiverFaceUrl", -- 接收人头像路径
		n."ReceiveTime", -- 接收时间
		n."ReadTime", -- 阅读时间
		n."Urgency", -- 紧急程度
		n."Group", -- 分组
		n."Title", -- 通知标题
		n."Summary", -- 通知消息摘要
		n."BodyType", -- 正文类型代码
		n."FuncUrl", -- 目标功能路径
		n."AttachmentId", -- 附件 Id
		a."Url" as "AttachmentUrl", -- 附件文件路径
		a."Name" as "AttachmentName" -- 附件文件名称
	from
		"waln_notifying"."notifications" as n
	join
		"waln_authority"."users" as u on u."Id" = n."ReceiverId"
	left join 
		"waln_notifying"."attachments" as a on a."Id" = n."AttachmentId";
go

/*
 * # 对象视图 - Sql Server:
 *   Attachment - 通知附件
 * 
 * # 描述:
 *   表示接收消息的一个附件文件。
 */

if object_id(N'"waln_notifying"."v_outbox_attachment"', N'V') is null
	exec('create view "waln_notifying"."v_outbox_attachment" as select null as "_null"');
go

alter view "waln_notifying"."v_outbox_attachment"
as
	select
		a."Id", -- Id
		a."Name", -- 文件名称
		a."Mime", -- MIME 类型
		a."Size", -- 附件文件的大小
		a."Url", -- 文件路径
		a."UploaderId", -- 上传人的 Id
		a."UploadTime" -- 上传时间
	from
		"waln_notifying"."attachments" as a;
go

/*
 * # 对象视图 - Sql Server:
 *   Notification - 通知
 * 
 * # 描述:
 *   一条已经发送或接收到的通知消息。
 */

if object_id(N'"waln_notifying"."v_service_notification"', N'V') is null
	exec('create view "waln_notifying"."v_service_notification" as select null as "_null"');
go

alter view "waln_notifying"."v_service_notification"
as
	select
		n."PK",
		n."Id", -- Id
		n."SenderId", -- 发送用户的 Id
		n."ReceiverId", -- 接收用户的 Id
		u."Name" as "ReceiverName", -- 接收人名称
		u."FaceUrl" as "ReceiverFaceUrl", -- 接收人头像路径
		n."ReceiveTime", -- 接收时间
		n."ReadTime", -- 阅读时间
		n."Urgency", -- 紧急程度
		n."Group", -- 分组
		n."Title", -- 通知标题
		n."Summary", -- 通知消息摘要
		n."Body", -- 通知正文内容
		n."BodyType", -- 正文类型代码
		n."FuncUrl", -- 目标功能路径
		n."RefDataType", -- 引用数据的类型
		n."RefDataKey", -- 引用数据键值
		n."AttachmentId" -- 附件 Id
	from
		"waln_notifying"."notifications" as n
	join
		"waln_authority"."users" as u on u."Id" = n."ReceiverId";
go

/*
 * # 对象视图 - Sql Server:
 *   Attachment - 通知附件
 * 
 * # 描述:
 *   表示接收消息的一个附件文件。
 */

if object_id(N'"waln_notifying"."v_service_attachment"', N'V') is null
	exec('create view "waln_notifying"."v_service_attachment" as select null as "_null"');
go

alter view "waln_notifying"."v_service_attachment"
as
	select
		a."Id", -- Id
		a."Name", -- 文件名称
		a."Mime", -- MIME 类型
		a."Size", -- 附件文件的大小
		a."Url", -- 文件路径
		a."UploaderId", -- 上传人的 Id
		a."UploadTime" -- 上传时间
	from
		"waln_notifying"."attachments" as a;
go
